﻿Imports knfUpdateDataLayer
Imports knfUpdateDataLayer.Aggiornamento


Public Class ProceduraReport

    Private _db As knfEntities
    Private _excelFilesDirectory As String
    Private _knaufLogoPath As String
    Private kReporters As New List(Of String)


    Public Sub New()

        _db = New knfEntities()

    End Sub

    Public Sub GeneraReport()

        Dim ListaVecchiReport As New List(Of GeneratedReports)
        _excelFilesDirectory = EstraiExcelDirectory()
        _knaufLogoPath = EstraiLogoPath()

        kReporters = EstraiReporters()

        ' Recuperiamo i vecchi Report
        ListaVecchiReport = RecuperaVecchiReport()

        ScriviNelDb("GENERATION", "Starting Report Creation")

        GeneraReportConSceltaMedia(False)
        GeneraReportConSceltaMedia(True)

        ScriviNelDb("GENERATION", "Ending Report Creation")

        ' Cancelliamo i vecchi Report

        ScriviNelDb("DELETING", "Starting Old Reports Cleaning")

        CancellaVecchiReport(ListaVecchiReport)

        ScriviNelDb("DELETING", "Ending Old Reports Cleaning")
    End Sub

    Private Function EstraiExcelDirectory() As String
        Try

            Dim excel = From p In _db.Update_Settings Where p.SettingName = "ExcelDirectory" Select p

            Return excel.ToList(0).SettingValue

        Catch ex As Exception

            Return String.Empty

        End Try
    End Function

    Private Function EstraiLogoPath() As String
        Try

            Dim excel = From p In _db.Update_Settings Where p.SettingName = "KnaufPNGLogoPath" Select p

            Return excel.ToList(0).SettingValue

        Catch ex As Exception

            Return String.Empty

        End Try
    End Function

    Private Function EstraiReporters() As List(Of String)
        Try
            Dim var = (From p In _db.Reporters Order By p.Reporter Select p.Reporter)
            Return var.ToList
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Private Function RecuperaVecchiReport() As List(Of GeneratedReports)
        Try
            Dim reports = From p In _db.GeneratedReports Where p.user_name = "davide" Select p
            Dim ListaReports As New List(Of GeneratedReports)
            ListaReports = reports.ToList()
            reports = Nothing

            Return ListaReports

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Private Sub ScriviNelDb(ByVal section As String, ByVal message As String)

        Dim localProcedura As New ProceduraAggiornamento
        localProcedura.ScriviNelDb(section, message)

        If (section = "ERROR" Or section = "CLOSING") Then
            localProcedura.ChiudiUltimaProceduraAperta()
        End If

        localProcedura = Nothing

    End Sub

    Private Sub GeneraReportConSceltaMedia(ByVal EseguiMediaBool As Boolean)

        Dim ii As Integer = 0

        For ii = 0 To kReporters.Count - 1
            Dim messaggio As String = "Creating Reports for " + kReporters(ii)
            ScriviNelDb("GENERATION", messaggio)
            CreaTuttiIReportRelativiAUnReporter(kReporters(ii), EseguiMediaBool)
        Next

    End Sub

    Private Sub CreaTuttiIReportRelativiAUnReporter(ByVal reporter As String, ByVal EseguiMediaBool As Boolean)

        Dim aliasProduct As String = String.Empty

        For i As Integer = 0 To 1

            Select Case i
                Case 0
                    aliasProduct = "Glass mineral wool"
                Case 1
                    aliasProduct = "Rock mineral wool"
            End Select

            'genera per tons
            GeneraReportPerProductPerCountry(aliasProduct, reporter, "Ton", EseguiMediaBool)
            'genera per euro
            GeneraReportPerProductPerCountry(aliasProduct, reporter, "Euro", EseguiMediaBool)
        Next

    End Sub

    Private Function EstraiListaAnni() As List(Of Integer)

        Dim tmpListaAnni As New List(Of Integer)
        Dim Anni = From p In _db.Update_Settings Where p.SettingName = "AnnoImportExport" Order By p.SettingID Select p

        For Each tmpAnno As Update_Settings In Anni
            tmpListaAnni.Add(Convert.ToInt16(tmpAnno.SettingValue))
        Next

        Anni = Nothing

        Return tmpListaAnni

    End Function


    Private Sub GeneraReportPerProductPerCountry(ByVal aliasProduct As String, ByVal reporter As String, ByVal ReportType As String,
                                         ByVal EseguiMediaBool As Boolean)


        Dim ListaAnni As New List(Of Integer)

        ListaAnni = EstraiListaAnni()

        Dim gHelper As New excelHelper(reporter, aliasProduct, ReportType, ListaAnni, EseguiMediaBool)

        Dim path As String = gHelper.CreaReport(_excelFilesDirectory, _knaufLogoPath)

        Dim localObject As New GeneratedReports
        With localObject
            .user_name = "davide"
            .report_date = Now.Date

            If EseguiMediaBool = True Then
                .report_title = aliasProduct + " - " + ReportType + " Averaged"

                .report_description = "Averaged: " + reporter + " - " + aliasProduct + " - " + ReportType + " - All Years, All Fluxes"


            Else
                .report_title = aliasProduct + " - " + ReportType + " Not Averaged"

                .report_description = "Not Averaged: " + reporter + " - " + aliasProduct + " - " + ReportType + " - All Years, All Fluxes"


            End If

            .report_url = path

            .report_admin = True
        End With

        _db.Configuration.AutoDetectChangesEnabled = False
        _db.GeneratedReports.Add(localObject)
        _db.SaveChanges()


        If localObject.report_id <> 0 Then
            Dim objRepCountries As New GeneratedCountries


            Dim idreporterList = From p In _db.Reporters Where p.Reporter = reporter Select p

            Dim idreporter As Integer = idreporterList.ToList(0).ReporterID


            objRepCountries.genCountryStato = True
            objRepCountries.report_id = localObject.report_id
            objRepCountries.ReporterID = idreporter

            _db.GeneratedCountries.Add(objRepCountries)
            _db.SaveChanges()
        End If

    End Sub


    Private Sub CancellaVecchiReport(ByVal ListaVecchiReport As List(Of GeneratedReports))

        For Each tmpReport As GeneratedReports In ListaVecchiReport

            Dim ReportID As Integer
            Dim ReportPath As String

            ReportID = tmpReport.report_id
            ReportPath = tmpReport.report_url

            CancellaGeneratedCountries(ReportID)
            CancellaGeneratedReport(ReportID)

            ' Cancellazione del file
            CancellaReportDalDisco(ReportPath)

        Next

    End Sub

    Private Sub CancellaGeneratedCountries(ByVal ReportID As Integer)
        ' Query the database for the rows to be deleted.
        Dim deleteTotali = _
            From totali In _db.GeneratedCountries
            Where totali.report_id = ReportID
            Select totali

        For Each totale As GeneratedCountries In deleteTotali
            _db.GeneratedCountries.Remove(totale)
        Next

        Try
            _db.SaveChanges()
        Catch ex As Exception

        End Try
    End Sub

    Private Sub CancellaGeneratedReport(ByVal ReportID As Integer)
        ' Query the database for the rows to be deleted.
        Dim deleteTotali = _
            From totali In _db.GeneratedReports
            Where totali.report_id = ReportID
            Select totali

        For Each totale As GeneratedReports In deleteTotali
            _db.GeneratedReports.Remove(totale)
        Next

        Try
            _db.SaveChanges()
        Catch ex As Exception

        End Try
    End Sub

    Private Sub CancellaReportDalDisco(ByVal FilePath As String)

        Try

            If System.IO.File.Exists(FilePath) = True Then
                System.IO.File.Delete(FilePath)
            End If

        Catch ex As Exception

        End Try


    End Sub

End Class
